CREATE OR REPLACE package body REACTION.API_FM_REPORT_PHATPT is
  -- Author  : PhatPT
  -- Created : 08.01.2014
  -- Purpose : API's for SVFM Report
  -- Public type declarations
  -- Public constant declarations
  -- Public variable declarations
  -- Public function and procedure declarations
  -- Function and procedure implementations
-->
procedure ALERT_STAT_BY_RULE_REPORT(
   i_date_from          IN DATE,
   i_date_to            IN DATE,
   o_dataset            OUT sys_refcursor
) is 
begin
    OPEN o_dataset FOR
        SELECT  RULE_ID, RULE_NAME, INSTITUTION_ID, TO_CHAR(DATE_TIME,'DD/MM/YYYY') DATE_TIME, HPAN,
                COUNT(CASE ALERTS WHEN 0 THEN NULL ELSE ALERTS END) ALERTS, 
                COUNT(CASE POSITIVE_ALERTS WHEN 0 THEN NULL ELSE POSITIVE_ALERTS END) POSITIVE_ALERTS,
                COUNT(CASE CONIFRMED_FRAUDULENT_TRANS WHEN 0 THEN NULL ELSE CONIFRMED_FRAUDULENT_TRANS END) CONIFRMED_FRAUDULENT_TRANS 
        FROM 
        (
            SELECT rcr.RULE_ID, rcr.RULE_NAME, tr.INSTITUTION_ID, tr.DATE_TIME, SUBSTR(tr.HPAN, 1, 6) HPAN,
                   al.ID ALERTS, 0 POSITIVE_ALERTS, 0 CONIFRMED_FRAUDULENT_TRANS
            FROM   TRANS tr
                   INNER JOIN ALERT al ON tr.ID = al.TRANSACTION_ID
                   INNER JOIN RULE_CHECK_RESULT rcr ON al.ID = rcr.ALERT_ID
            WHERE  rcr.RULE_ID IS NOT NULL
            
            UNION
            
            SELECT rcr.RULE_ID, rcr.RULE_NAME, tr.INSTITUTION_ID, tr.DATE_TIME, SUBSTR(tr.HPAN, 1, 6) HPAN,
                   0 ALERTS, al.ID, 0 CONIFRMED_FRAUDULENT_TRANS
            FROM   TRANS tr
                   INNER JOIN ALERT al ON tr.ID = al.TRANSACTION_ID
                   INNER JOIN RULE_CHECK_RESULT rcr ON al.ID = rcr.ALERT_ID
                   INNER JOIN INVESTIGATION inv ON al.ID = inv.ALERT_ID
            WHERE  rcr.RULE_ID IS NOT NULL
                   AND inv.ALERT_ID IS NOT NULL
                   AND inv.FRAUD_TYPE_ID IS NOT NULL
                   
            UNION
            
            SELECT rcr.RULE_ID, rcr.RULE_NAME, tr.INSTITUTION_ID, tr.DATE_TIME, SUBSTR(tr.HPAN, 1, 6) HPAN,
                   0 ALERTS, 0 POSITIVE_ALERTS, tf.ID CONIFRMED_FRAUDULENT_TRANS
            FROM   TRANS tr
                   INNER JOIN ALERT al ON tr.ID = al.TRANSACTION_ID
                   INNER JOIN RULE_CHECK_RESULT rcr ON al.ID = rcr.ALERT_ID
                   INNER JOIN INVESTIGATION inv ON al.ID = inv.ALERT_ID
                   INNER JOIN TRANS_FRAUD tf ON tr.ID = tf.TRANSACTION_ID AND inv.ID = tf.INVESTIGATION_ID
        )
        WHERE NVL(DATE_TIME, SYSDATE - 1) BETWEEN NVL(i_date_from, SYSDATE - 365) AND NVL(i_date_to, SYSDATE + 365)
        GROUP BY RULE_ID, RULE_NAME, INSTITUTION_ID, TO_CHAR(DATE_TIME,'DD/MM/YYYY'), HPAN
        ORDER BY RULE_ID;
    
end ALERT_STAT_BY_RULE_REPORT;

end API_FM_REPORT_PHATPT;
/